let endtimeNo=$#
let starttimeNo=$#-1
eval END_TIMEKEY=\$$endtimeNo
eval START_TIMEKEY=\$$starttimeNo

#local_ip=`ip addr|grep bond0|grep 255|awk '{print$2}'|awk -F'/' '{print$1}'`
#ip_exist=`cat /RPT/etlscript/flume/conf/flume_server_list.conf | grep $local_ip | wc -l`
#if [ $ip_exist = 1 ];then
#   echo "The IP($local_ip) can NOT run sqoop_import_source_data.sh !"
#   echo "This shell is only running on impalaD servers (172.22.6.126 - 139)"
#   exit 1
#fi

CONST_SITE=`echo $1|tr a-z A-Z|awk -F'/' '{print $8}'|awk -F'_' '{print $1}'`
used_sql=`echo $1|sed 's/\//|/g'`
dest_table=`echo $1|awk -F"sqoop_t_|.sql" '{print $3}' | cut -d '-' -f1 `
dest_table_desc=`echo $1|awk -F"sqoop_t_|.sql" '{print $3}'`
dest_table_4=`echo ${dest_table}|awk -F'_' '{print $1"_"$2"_"$3"_"$4}'`
dest_table_2=`echo ${dest_table}|awk -F'_' '{print $1"_"$2}'`


if [[ "${dest_table}" =~ ^s.* ]]; then
 if [ "${dest_table_2}" = "s_eda" ];then
  dest_table_4x="s_eda"
 elif [ "${dest_table_2}" = "s_oem" ];then
  dest_table_4x="s_oem"
 fi
else
 if [ "${dest_table_4}" = "i_d_r_mt" ];then
   dest_table_4x="i_d_r_mt"
 elif [ "${dest_table_4}" = "i_d_r_eyms" ];then
   dest_table_4x="i_d_r_eyms"
 elif [ "${dest_table_4}" = "i_f_r_sap" ];then
   if [ "${dest_table}" = "i_f_r_sap_hist_tgt_input_output" ];then
   dest_table_4x="i_f_r_sap_hist_tgt"
   else
   dest_table_4x="i_f_r_sap"
   fi
 else
   dest_table_4x=`echo ${dest_table}|awk -F'_' '{print $1"_"$2"_"$3"_xxx"}'`
 fi
fi

dest_table_5x=`echo ${dest_table}|awk -F'_' '{print $1"_"$2"_"$3"_xxx_"$5}'`
hive_table=`echo $1|awk -F"sqoop_t_|.sql" '{print "sqoop_t_"$3}' | cut -d '-' -f1 `
db_tns_alias=`echo $1|awk -F"sql/|/sqoop" '{print $3}'`
path_table_columns=`echo $1|awk -F'/sqoop_t' '{print $1}' `

where_is_shell=/RPT/etlscript/sqoop/shell
where_is_conf=/RPT/etlscript/sqoop/conf

source /RPT/etlscript/hive/function/init_date_function ${START_TIMEKEY} ${END_TIMEKEY}

if [ ! -f "$1" ];then
        echo "The sqlfile \"$1\" is not exist."
        exit 1
else
        hadoop fs -test -e /data/db/rptptm/${hive_table}
        if [ $? -eq 1 ] ;then
           echo "The table rptptm.${hive_table} is not exist."
           exit 1
        else
           hadoop fs -test -e /data/db/rptptm/${hive_table}/used_sql=${used_sql}
           if [ $? -eq 1 ] ;then
              echo "The partition(used_sql=${used_sql}) of rptptm.${hive_table} is not exist."
              echo "Please ADD PARTITION first. Use hive sql \"ALTER TABLE rptptm.${hive_table} ADD PARTITION (used_sql='${used_sql}');\""
              exit 1
           fi
        fi
                connect_db_tns=`cat ${where_is_conf}/source_db_tns.conf | grep ${db_tns_alias} | awk -F '==' '{print$2}'`
                for ((retry=0;retry<3;retry++))
                do
                count_files=`hadoop fs -ls  /data/db/rptptm/${hive_table}/used_sql=${used_sql} | wc -l`
                if [ $count_files -gt 0 ];then
                        hadoop fs -rm -r /data/db/rptptm/${hive_table}/used_sql=${used_sql}/*
                fi

                query_sql_scripts=`echo \`cat $1\`| awk -F';' '{print $1}'`
                query_sql=`echo ${query_sql_scripts}|sed s/JVVI_START_TIMEKEY/${START_TIMEKEY}/g|sed s/JVVI_END_TIMEKEY/${END_TIMEKEY}/g|sed s/GVS_START_SHIFT_TIMEKEY/"${GVS_START_SHIFT_TIMEKEY}"/g|sed s/GVS_END_SHIFT_TIMEKEY/"${GVS_END_SHIFT_TIMEKEY}"/g|sed s/GVS_START_DATE_TIMEKEY/${GVS_START_DATE_TIMEKEY}/g|sed s/GVS_END_DATE_TIMEKEY/${GVS_END_DATE_TIMEKEY}/g|sed s/START_END_TIMEKEY/${START_TIMEKEY}-${END_TIMEKEY}/g|sed s/CONST_SITE/${CONST_SITE}/g`
                sqoop import -D mapred.child.java.opts="-Djava.security.egd=file:/dev/./dev/urandom" -D oraoop.jdbc.url.verbatim=true -Dsqoop.parquet.logical_types.decimal.enable=false \
                --append --connect ${connect_db_tns} --query "${query_sql} and \$CONDITIONS" -m 1 \
                --target-dir  /data/db/rptptm/${hive_table}/used_sql=${used_sql} \
                --null-string '\\N' --null-non-string '\\N' --as-parquetfile --direct-split-size 134217728

                returncode=$?
                if [ ${returncode} -eq 0  -a  ${retry} -le 2 ] ; then
                   break
                else
                   sleep 5
                fi
                done

                echo "returncode=$returncode"
                if [ $returncode -ne 0 ];then
                echo "exit $returncode"
                exit  $returncode
                fi

                for((redo=0;redo<3;redo++))
                do
###             impala-shell -i 172.25.6.200 -q "refresh rptptm.${hive_table};"
                impala-shell -i 172.25.6.200 -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "refresh rptptm.${hive_table};"
                returncode1=$?
                interface_time="'"`date -d today +"%Y-%m-%d %T"`"'"
                if [ ! -d ${path_table_columns}/.table_columns ];then
                  mkdir ${path_table_columns}/.table_columns
                fi
###             impala-shell -i 172.25.6.200 -q "desc rptpid.${dest_table};" -o ${path_table_columns}/.table_columns/${dest_table_desc}.desc
                if [[ "${dest_table}" =~ ^s.* ]]; then
                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "desc rptpsd.${dest_table};" -o ${path_table_columns}/.table_columns/${dest_table_desc}.desc
                returncode2=$?
                else
                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "desc rptpid.${dest_table};" -o ${path_table_columns}/.table_columns/${dest_table_desc}.desc
                returncode2=$?
                fi
                for i in ${dest_table_4} ${dest_table_4x} ${dest_table_5x} ; do
                  if [ "$i" = "i_d_r_mt" -o "$i" = "i_d_r_eyms" -o "$i" = "i_f_r_mt"  ] ; then
                    for_upsert=4
                    break
                  elif [ "$i" = "i_f_r_sap" -o "$i" = "s_eda" ] ; then
                    ###for_upsert=4
                    for_upsert="5|'CONST_SITE',"
                    break
                  else
                    for j in `cat ${where_is_conf}/for_upsert.properties | grep $i`; do
                      if [ "`echo $j|awk -F '=' '{print$1}'`" = "$i" ] ; then
                        for_upsert=`echo $j|awk -F '=' '{print$2}' | sed 's/ //g'`
                      fi
                    done
                  fi
                done
                head_count=`echo ${for_upsert}|cut -d '|' -f1`
                own_columns_temp=`echo ${for_upsert}|awk -F'|' '{print $2}'`
                own_columns=`echo ${own_columns_temp}|sed s/CONST_SITE/${CONST_SITE}/g|sed s/END_TIMEKEY/${END_TIMEKEY}/g`

                table_columns_count=`cat ${path_table_columns}/.table_columns/${dest_table_desc}.desc | wc -l`
                sed_end=" ${head_count},`expr ${table_columns_count} - 1`p"

                column_list=`cat ${path_table_columns}/.table_columns/${dest_table_desc}.desc | awk -F'|' '{print $2}' | sed 's/ //g' | sed -n "${sed_end}" |sed 's/^/\`&/g'|sed 's/$/\`&/g' | sed ':t;N;s/\n/,/;b t' | sed 's/\`interface_time\`/'"${interface_time}"'/g'`

                delete_sql_scripts=`echo \`cat $1\`| awk -F';' '{print $2}'`
                returncode3=0
                if [ -n "${delete_sql_scripts}" ] ; then
                delete_sql=`echo ${delete_sql_scripts}|sed s/JVVI_START_TIMEKEY/${START_TIMEKEY}/g|sed s/JVVI_END_TIMEKEY/${END_TIMEKEY}/g|sed s/GVS_START_SHIFT_TIMEKEY/"${GVS_START_SHIFT_TIMEKEY}"/g|sed s/GVS_END_SHIFT_TIMEKEY/"${GVS_END_SHIFT_TIMEKEY}"/g|sed s/GVS_START_DATE_TIMEKEY/${GVS_START_DATE_TIMEKEY}/g|sed s/GVS_END_DATE_TIMEKEY/${GVS_END_DATE_TIMEKEY}/g|sed s/START_END_TIMEKEY/${START_TIMEKEY}-${END_TIMEKEY}/g|sed s/CONST_SITE/${CONST_SITE}/g`

                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "${delete_sql};"
                returncode3=$?
                fi

####            impala-shell -i 172.25.6.200 -q "refresh rptptm.${hive_table}; upsert into rptpid.${dest_table} select ${own_columns} ${column_list} from rptptm.${hive_table} where used_sql='${used_sql}';"
                if [[ "${dest_table}" =~ ^s.* ]]; then
                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "refresh rptptm.${hive_table}; upsert into rptpsd.${dest_table} select ${own_columns} ${column_list} from rptptm.${hive_table} where used_sql='${used_sql}';"
                returncode4=$?
                elif [[ "${dest_table}" == *hist*by_id* ]] ; then
                sed_end=" 4,`expr ${table_columns_count} - 1`p"
                column_list_by_id=`cat ${path_table_columns}/.table_columns/${dest_table_desc}.desc | awk -F'|' '{print $2}' | sed 's/ //g' | sed -n "${sed_end}" |sed 's/^/\`&/g'|sed 's/$/\`&/g' | sed ':t;N;s/\n/,/;b t' | sed 's/\`interface_time\`/'"${interface_time}"'/g'|sed 's/\`site\`,\`shift_timekey\`,/'\'${CONST_SITE}\'',rptpid.f_get_shift_timekey(event_timekey),/g'`
                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "refresh rptptm.${hive_table}; upsert into rptpid.${dest_table} select ${column_list_by_id} from rptptm.${hive_table} where used_sql='${used_sql}';"
                returncode4=$?
                else
                impala-shell -i 172.25.6.200  -l --auth_creds_ok_in_clear -u csot.rptedtetl --ldap_password_cmd="/RPT/etlscript/sqoop/shell/sqoop_impala_shell_ldap_password.sh" -q "refresh rptptm.${hive_table}; upsert into rptpid.${dest_table} select ${own_columns} ${column_list} from rptptm.${hive_table} where used_sql='${used_sql}';"
                returncode4=$?
                fi
      if [ $redo -lt 2 ];then
        if [ $returncode1 -eq 0 -a $returncode2 -eq 0 -a $returncode3 -eq 0 -a $returncode4 -eq 0 ];then
                echo "exit 0"
                exit 0
        else
                echo "step1=$returncode1"
                echo "step2=$returncode2"
                echo "step3=$returncode3"
                echo "step4=$returncode4"
                echo "first execute end!!!"
                sleep 90
        fi
      else
        if [ $returncode1 -ne 0 ];then
                echo "step1=$returncode1"
                exit $returncode1
        fi
        if [ $returncode2 -ne 0 ];then
                echo "step2=$returncode2"
                exit $returncode2
        fi
        if [ $returncode3 -ne 0 ];then
                echo "step3=$returncode3"
                exit $returncode3
        fi
        if [ $returncode4 -ne 0 ];then
                echo "step4=$returncode4"
                exit $returncode4
        fi
      fi
     done
    fi

